package com.dao;

import com.bean.Salary;
import com.common.DbConnect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class SalaryDao {

    //增加工资单
    public void addSalary(Salary salary) {
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;

        String sql = "insert into salary(id,dnumber,name,isManager,base,salesVolumn,commision,tax,salary,miaoshu)"
                + "values(?,?,?,?,?,?,?,?,?,?)";
        try {
            st = cn.prepareStatement(sql);

            st.setString(1, salary.getId());
            st.setString(2, salary.getDnumber());
            st.setString(3, salary.getName());
            st.setBoolean(4, salary.isManager());
            st.setDouble(5, salary.getBase());
            st.setDouble(6, salary.getSalesVolumn());
            st.setDouble(7, salary.getCommision());
            st.setDouble(8,salary.getTax());
            st.setDouble(9,salary.getSalary());
            st.setString(10,salary.getMiaoshu());

            st.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbConnect.close(cn, st, null);
        }
    }

    //删除工资单
    public void deleteSalary(String sid){
        Connection cn= DbConnect.getConnection();
        PreparedStatement st = null;
        String sql = "delete from salary where sid = " + sid;
        try {
            st = cn.prepareStatement(sql);
            st.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, null);
        }

    }
    public List<Salary> getSalaryListByDeptYear(String deptid,String year){
        ArrayList<Salary> salarys = new ArrayList<Salary>();
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            //select * from day_rate where year(时间字段)='2020
            //select * from day_rate where year(时间字段)='2019' and month(时间字段)='02'
            String sql = "select * from salary where dnumber=? and year(date)=?";
            st = cn.prepareStatement(sql);
            st.setString(1, deptid);
            st.setString(2, year);

            rs = st.executeQuery();
            while (rs.next()) {
                Salary salary = new Salary(rs.getInt("sid"),rs.getString("id"),rs.getString("name"), rs.getString("miaoshu"), rs.getDate("date")
                        ,rs.getString("dnumber"),rs.getBoolean("isManager"),rs.getDouble("base")
                        ,rs.getDouble("salesVolumn"),rs.getDouble("commision"),rs.getDouble("tax"),
                        rs.getDouble("salary"));
                salarys.add(salary);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return salarys;
    }
    public List<Salary> getSalary(String year){
        ArrayList<Salary> salarys = new ArrayList<Salary>();
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            //select * from day_rate where year(时间字段)='2020
            //select * from day_rate where year(时间字段)='2019' and month(时间字段)='02'
            String sql = "select * from salary where year(date)=?";
            st = cn.prepareStatement(sql);
            st.setString(1, year);

            rs = st.executeQuery();
            while (rs.next()) {
                Salary salary = new Salary(rs.getInt("sid"),rs.getString("id"),rs.getString("name"), rs.getString("miaoshu"), rs.getDate("date")
                        ,rs.getString("dnumber"),rs.getBoolean("isManager"),rs.getDouble("base")
                        ,rs.getDouble("salesVolumn"),rs.getDouble("commision"),rs.getDouble("tax"),
                        rs.getDouble("salary"));
                salarys.add(salary);
            }
            for (Salary h : salarys) {
                System.out.println(h.getName()+" ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return salarys;
    }
    public List<Salary> getSalary(String year,String month){
        ArrayList<Salary> salarys = new ArrayList<Salary>();
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            //select * from day_rate where year(时间字段)='2020
            //select * from day_rate where year(时间字段)='2019' and month(时间字段)='02'
            String sql = "select * from salary where year(date)=? and month(date)=?;";
            st = cn.prepareStatement(sql);
            st.setString(1, year);
            st.setString(2, month);

            rs = st.executeQuery();
            while (rs.next()) {
                Salary salary = new Salary(rs.getInt("sid"),rs.getString("id"),rs.getString("name"), rs.getString("miaoshu"), rs.getDate("date")
                        ,rs.getString("dnumber"),rs.getBoolean("isManager"),rs.getDouble("base")
                        ,rs.getDouble("salesVolumn"),rs.getDouble("commision"),rs.getDouble("tax"),
                        rs.getDouble("salary"));
                salarys.add(salary);
            }
            for (Salary h : salarys) {
                System.out.println(h.getName()+" ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return salarys;
    }

    public List<Salary> getSalaryListByDept(String deptid,String year,String month){
        ArrayList<Salary> salarys = new ArrayList<Salary>();
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            //select * from day_rate where year(时间字段)='2020
            //select * from day_rate where year(时间字段)='2019' and month(时间字段)='02'
            String sql = "select * from salary where dnumber=? and year(date)=? and month(date)=?;";
            st = cn.prepareStatement(sql);
            st.setString(1, deptid);
            st.setString(2, year);
            st.setString(3, month);

            rs = st.executeQuery();
            while (rs.next()) {
                Salary salary = new Salary(rs.getInt("sid"),rs.getString("id"),rs.getString("name"), rs.getString("miaoshu"), rs.getDate("date")
                        ,rs.getString("dnumber"),rs.getBoolean("isManager"),rs.getDouble("base")
                        ,rs.getDouble("salesVolumn"),rs.getDouble("commision"),rs.getDouble("tax"),
                        rs.getDouble("salary"));
                salarys.add(salary);
            }
            for (Salary h : salarys) {
                System.out.println(h.getName()+" ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return salarys;
    }
    public List<Salary> getSalaryListByUserYear(String id,String year){
        ArrayList<Salary> salarys = new ArrayList<Salary>();
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            //select * from day_rate where year(时间字段)='2020
            //select * from day_rate where year(时间字段)='2019' and month(时间字段)='02'
            String sql = "select * from salary where id=? and year(date)=?;";
            st = cn.prepareStatement(sql);
            st.setString(1, id);
            st.setString(2, year);

            rs = st.executeQuery();
            while (rs.next()) {
                Salary salary = new Salary(rs.getInt("sid"),rs.getString("id"),rs.getString("name"), rs.getString("miaoshu"), rs.getDate("date")
                        ,rs.getString("dnumber"),rs.getBoolean("isManager"),rs.getDouble("base")
                        ,rs.getDouble("salesVolumn"),rs.getDouble("commision"),rs.getDouble("tax"),
                        rs.getDouble("salary"));
                salarys.add(salary);
            }
            for (Salary h : salarys) {
                System.out.println(h.getName()+" ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return salarys;
    }
    public List<Salary> getSalaryListByUser(String id,String year,String month){
        ArrayList<Salary> salarys = new ArrayList<Salary>();
        Connection cn = DbConnect.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;

        if (cn == null)
            return null;
        try {
            //4.执行sql
            //select * from day_rate where year(时间字段)='2020
            //select * from day_rate where year(时间字段)='2019' and month(时间字段)='02'
            String sql = "select * from salary where id=? and year(date)=? and month(date)=?;";
            st = cn.prepareStatement(sql);
            st.setString(1, id);
            st.setString(2, year);
            st.setString(3, month);

            rs = st.executeQuery();
            while (rs.next()) {
                Salary salary = new Salary(rs.getInt("sid"),rs.getString("id"),rs.getString("name"), rs.getString("miaoshu"), rs.getDate("date")
                        ,rs.getString("dnumber"),rs.getBoolean("isManager"),rs.getDouble("base")
                        ,rs.getDouble("salesVolumn"),rs.getDouble("commision"),rs.getDouble("tax"),
                        rs.getDouble("salary"));
                salarys.add(salary);
            }
            for (Salary h : salarys) {
                System.out.println(h.getName()+" ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭数据库连接
            DbConnect.close(cn, st, rs);
        }
        return salarys;
    }
}
